"""
py-opengauss <https://gitee.com/wjmcat/py-opengauss>

Authors:    Jingjing WU (吴京京) <https://gitee.com/wjmcat/>

2021-now @ Jingjing WU (吴京京)

Licensed under the Apache License, Version 2.0 (the 'License');
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an 'AS IS' BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
"""
from __future__ import annotations, with_statement

import os
import sys
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))
from typing import List, Tuple

from psycopg2 import connect
from psycopg2.extensions import connection
from faker import Faker

from examples import get_logger, check_table_exist
from examples.schema import (
    TableBase,
    TableDataNotExistError,
    TableNotExistError    
)


logger = get_logger()

def create_conn() -> connection:
    """get connection from envrionment variable by the conn factory

    Returns:
        [type]: the psycopg2's connection object
    """
    env = os.environ
    params = {
        'database': env.get('OG_DATABASE', 'opengauss'),
        'user': env.get('OG_USER', 'gaussdb'),
        'password': env.get('OG_PASSWORD', 'Secretpassword@123'),
        'host': env.get('OG_HOST', '127.0.0.1'),
        'port': env.get('OG_PORT', 5432)
    }
    conn: connection = connect(**params)
    return conn

class Connection:
    _instance = None

    @classmethod
    def instance(cls):
        if not cls._instance:
            cls._instance = create_conn()
        return cls._instance
            

class Student(TableBase):
    """The student table
    """
    __table_name__ = 'test_student'

    def __init__(self,
        name: str, 
        course: str, 
        grade: float,
        id: int = None
    ):
        """init the student object

        Args:
            name (str): the name of student
            course (str): the course of student
            grade (float): the grade of student
        """
        super().__init__()
        self.name = name
        self.course = course
        self.grade = grade
        self.id = id

    def __str__(self) -> str:
        return f"Student(id={self.id}, name={self.name}, course={self.course}, grade={self.grade})"

    @classmethod
    def init_database(cls):
        conn = Connection.instance()
        exist = check_table_exist(conn, Student.__table_name__)
        if not exist:
            with conn.cursor() as cursor:
                sql = f"""CREATE TABLE {cls.__table_name__} (id serial PRIMARY KEY, name varchar, course varchar, grade integer);"""
                cursor.execute(sql)
                conn.commit()
    
    def insert(self):
        """insert self data into the table
        """
        conn = Connection.instance()
        with conn.cursor() as cursor:
            sql = f"insert into {self.__table_name__} (name, course, grade) values (%s,%s,%s) RETURNING *;"
            cursor.execute(sql, (self.name, self.course, self.grade))
            data = cursor.fetchone()
            logger.success(f"insert data<{data}> into table<{self.__table_name__}>")
            self.id = data[0]
            conn.commit()
    
    def update(self) -> int:
        """update data from the database

        Returns:
            int: the final data of object
        """
        if not self.id:
            logger.error(f'there is not id in data<{self}>')
            raise ValueError(f'there is not id in data<{self}>')
        conn = Connection.instance()
        with conn.cursor() as cursor:
            sql = f"update {self.__table_name__} set name=%s, course=%s, grade=%s where id={self.id}"
            cursor.execute(sql, (self.name, self.course, self.grade))
            logger.success(f"update data<{self}>")
        conn.commit()
    
    def delete(self) -> int:
        """delete data

        Returns:
            int: the primary key of the table
        """
        if not self.id:
            logger.error(f'there is not id in data<{self}>')
            raise ValueError(f'there is not id in data<{self}>')
        conn = Connection.instance()
        with conn.cursor() as cursor:
            sql = f"delete from {self.__table_name__} where id={self.id}"
            cursor.execute(sql)
            logger.success(f"delete data<{self}>")
        conn.commit()

    @classmethod
    def get(cls, **kwargs) -> Tuple[Student, List[Student]]:
        """filter data from database

        Returns:
            Tuple[Student, List[Student]]: the final data
        """
        sql = f"select * from {cls.__table_name__}"
        params = []
        if kwargs:
            condition = ' and '.join([f"{key}=%s" for key in kwargs.keys()])
            sql = f"{sql} where {condition}"
            params = list(kwargs.values())
        
        conn = Connection.instance()
        with conn.cursor() as cursor:
            cursor.execute(sql, params)
            items = cursor.fetchmany()
            students = [Student(*item) for item in items]
            logger.success(f"filter data<{students}> by params<{kwargs}>")
        return students

if __name__ == "__main__":

    # 1. check and init the table
    Student.init_database()
    students = Student.get()
    for student in students:
        student.delete()

    # 2. insert the data
    xiaoming_student = Student(
        name='小明-123',
        course='数学',
        grade=84
    )
    xiaoming_student.insert()
    
    # 3. update the data
    xiaoming_student.name = '小明-1234'
    xiaoming_student.update()

    # 4. filter the data
    students = Student.get(name='小明-1234')
    assert len(students) == 1

    # 5. delete the data
    xiaoming_student.delete()
    students = Student.get(name='小明-1234')
    assert len(students) == 0
